# !/usr/bin/env python
# -*-coding:utf-8 -*-

"""
# Time       ：2022/2/12
# Author     ：xuzhanhong
# Description：数据查询，过滤，修改
"""
import dash
import dash_bootstrap_components as dbc
import dash_core_components as dcc
import dash_html_components as html
import dash_table
from dash.dependencies import Input, Output, State

from flask import send_from_directory

import os
import uuid
from sqlalchemy import create_engine
import pandas as pd

from config.config import global_config
from server import app


try:
    os.mkdir("downloads")
except FileExistsError:
    pass


# 数据库配置
db = global_config['db']
db_url = db['url']
base_schema = db['base_schema']
postgres_url = os.path.join(db_url, base_schema)
engine = create_engine(postgres_url)

# app = dash.Dash(__name__)


@app.server.route('/download/<file>')
def download(file):
    return send_from_directory('downloads', file)


def refresh_schema_names():
    schema_names = pd.read_sql_query("show schemas;", con=engine)
    return [{'label': name, 'value': name} for name in
            schema_names['Database']]


def schema2table(schema):
    if not schema:
        schema = base_schema
    postgres_url = os.path.join(db_url, schema)
    engine = create_engine(postgres_url)
    return [
        {
            'label': table,
            'value': table
        }
        for table in pd.read_sql_query('SHOW TABLES', con=engine)[f'Tables_in_{schema}']
    ]


table_page = html.Div(
    [
        html.Br(),
        dbc.Container(
            [
                dbc.Row(
                    [
                        dbc.Col(
                            dcc.Dropdown(
                                placeholder='选择库',
                                id='select_schema',
                                options=refresh_schema_names(),
                            ),
                            width=4
                        ),
                        dbc.Col(
                            dcc.Dropdown(
                                placeholder='选择表',
                                id='select_table',
                            ),
                            width=4
                        ),
                        dbc.Col(
                            dbc.Button(
                                '查询数据',
                                id='get-data',
                                n_clicks=0,
                            ),
                            width=2
                        ),
                        dbc.Col(
                            html.A(id='download-url', target="_blank"),
                            width=2
                        ),
                    ]
                ),
                html.Hr(),
                dash_table.DataTable(
                    id='dash-table',
                    editable=True,
                    page_size=15,
                    style_header={
                        'font-family': 'Times New Roman',
                        'font-weight': 'bold',
                        'text-align': 'center'
                    },
                    style_data={
                        'font-family': 'Times New Roman',
                        'text-align': 'center'
                    },
                    style_data_conditional=[
                        {
                            # 对选中状态下的单元格进行自定义样式
                            "if": {"state": "selected"},
                            "background-color": "#b3e5fc",
                            "border": "none"
                        },
                    ],
                    filter_action="native"
                ),
                dbc.Button('同步变动到数据库', id='update-tables', style={'display': 'none'}),
                html.P(id='message')
            ]
        ),
    ]
)


@app.callback(
    Output('select_table', 'options'),
    Input('select_schema', 'value')
)
def refresh_tables(schema):
    print(schema)
    if schema:
        return schema2table(schema)
    return dash.no_update


@app.callback(
    [Output('dash-table', 'data'),
     Output('dash-table', 'columns'),
     Output('update-tables', 'style')],
    Input('get-data', 'n_clicks'),
    [State('select_schema', 'value'),
     State('select_table', 'value')],
     prevent_initial_call=True
)
def render_dash_table(n_clicks, select_schema, select_table):
    print(select_schema, select_table)
    if select_schema and select_table:
        postgres_url = os.path.join(db_url, select_schema)
        engine = create_engine(postgres_url)
        df = pd.read_sql_table(select_table, con=engine)

        return df.to_dict('records'), [
            {'name': column, 'id': column}
            for column in df.columns
        ], {'margin-top': '25px'}

    else:
        return [], [], {'display': 'none'}


@app.callback(
    [Output("download-url", "href"),
     Output("download-url", "children")],
    [Input("dash-table", "derived_virtual_data"),
     Input("dash-table", "filter_query")],
    prevent_initial_call=True
)
def download_table(derived_virtual_data, filter_query):
    if derived_virtual_data:
        filename = f"output_{uuid.uuid1()}.xlsx"

        pd.DataFrame(derived_virtual_data).to_excel("downloads/" + filename, index=False)

        return "/download/" + filename, "下载当前状态表格"

    return "", ""


@app.callback(
    [Output('message', 'children'),
     Output('message', 'style')],
    Input('update-tables', 'n_clicks'),
    [State('dash-table', 'data'),
     State('select_schema', 'value'),
     State('select_table', 'value')]
)
def update_to_database(n_clicks, data, select_schema, select_table):
    print(select_schema, select_table)

    if n_clicks:

        try:
            pd.DataFrame(data).to_sql(select_table, schema=select_schema, con=engine, if_exists='replace', index=False)

            return '更新成功！', {'color': 'green'}
        except Exception as e:
            return f'更新失败！{e}', {'color': 'red'}

    return dash.no_update


# if __name__ == '__main__':
#     app.run_server(debug=True)
